package com.qc.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import com.mysql.jdbc.Statement;

public class DBHandler {

	public Connection conn = null;
	public PreparedStatement pstmt = null;
	public ResultSet rs = null;

	/**
	 * @throws SQLException 
	 * update:对数据库增删改操作
	 * 
	 * @param @param sql
	 * @param @param list
	 * @param @param dataSource
	 * @param @return
	 * @return int
	 * @throws
	 */
	public long update(String sql, List<Object> list, DataSource dataSource) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int count = 0;
		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			int size = list.size();
			if (list != null && size > 0) {
				for (int i = 0; i < size; i++) {
					pstmt.setObject(i + 1, list.get(i));
				}
			}
			System.out.println(pstmt);
			Util.logDailyInfo(pstmt);
			count = pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys(); 
			if (rs.next()) { 
				return rs.getLong(1) ; 
			}
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (Exception e) {

			}
		}
		return count;
	}

	/**
	 * query:对数据库查询操作
	 * 
	 * @param @param sql
	 * @param @param list
	 * @param @param dataSource
	 * @param @return
	 * @return ResultSet
	 * @throws
	 */
	public ResultSet query(String sql, List<Object> list, DataSource dataSource) {
		if (dataSource != null) {
			try {
				conn = dataSource.getConnection();
				pstmt = conn.prepareStatement(sql);
				if (list != null) {
					for (int i = 0; i < list.size(); i++) {
						pstmt.setObject(i + 1, list.get(i));
					}
				}
				System.out.println(pstmt);
				Util.logDailyInfo(pstmt);
				rs = pstmt.executeQuery();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rs;
	}

	/**
	 * closeAll:关闭连接
	 * 
	 * @param
	 * @return void
	 * @throws
	 */
	public void closeAll() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) throws SQLException {
		String JDriver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://rm-uf67o0kl4b7c25dx1o.mysql.rds.aliyuncs.com:3306/xls6688";
		String user="dojochina";
		String password="Hoboo88!";
		try{
			Class.forName(JDriver);// 动态导入数据库的驱动
			Connection conn=DriverManager.getConnection(url, user, password);// 获取数据库链接 
			String query="SELECT * FROM zc_addresses ";// 创造SQL语句 
			Statement stmt= (Statement) conn.createStatement();// 执行SQL语句
			ResultSet rs=stmt.executeQuery(query);
			while(rs.next()){
				System.out.println(rs.getString("name"));
				//密码字段的编号从1开始，密码排第二位
			}
			System.out.println("查询数据成功");
			rs.close();
			stmt.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}
